package batch.udb;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.ObjectUtils;
import org.apache.log4j.*;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.*;
import java.io.*;
import java.util.Date;
import java.util.Calendar;

public class makeUserFileForGbrCRM
{
    private static String sTmpDate = "";

    public makeUserFileForGbrCRM() {
        ;
    }

    public static void main(String [] args) {
        Logger.getLogger("udb.makeUserFileForGbrCRM").info("( * ) Start.");

        if(args.length == 1){
            sTmpDate = args[0];
        }

        makeUserFileForGbrCRM mkUser = new makeUserFileForGbrCRM();
        mkUser.startMake();

        Logger.getLogger("udb.makeUserFileForGbrCRM").info("( * ) End.");
    }

    //Make a log file and a data file.
    public void startMake(){
        Connection   conn = null;
        Statement    stmt = null;
        ResultSet    rs   = null;
        OutputStreamWriter oswFile = null;

        Calendar calen = Calendar.getInstance();

        String TODAY     = "";
        String YESTERDAY = "";

        Date ye1day = null;

        if(sTmpDate.equals("") || sTmpDate == null){
            calen.add(Calendar.DATE, -1);
            ye1day = calen.getTime();

            TODAY = DateFormatUtils.format(new Date(), "yyyyMMdd");
            YESTERDAY = DateFormatUtils.format(ye1day, "yyyyMMdd");
        }
        else{
            TODAY = sTmpDate;

            java.text.SimpleDateFormat sformat = new java.text.SimpleDateFormat("yyyyMMdd");
            java.util.Date dTmpDate = sformat.parse(TODAY, new java.text.ParsePosition(0));

            calen.setTime(dTmpDate);
            calen.add(Calendar.DATE, -1);
            ye1day = calen.getTime();

            YESTERDAY = DateFormatUtils.format(ye1day, "yyyyMMdd");
        }

        String fileNm  = "/home01/sfc/batch/udb/data/mbr_"  + TODAY;

        int cnt = 0;
        int rsCnt = 0;

        StringBuffer sbTemp = new StringBuffer();

        StringBuffer sQuery = new StringBuffer()
                             .append("SELECT A.USER_NO                                                                          	\n")
                             .append("     , ID                                                                                 	\n")
                             .append("     , PASSWORD_NEW                                                                       	\n")
                             .append("     , DECODE(FIRSTNAME, 'WAP_TEMP', ''                                                   	\n")
                             .append("                       , 'SIM_TEMP', ''                                                   	\n")
                             .append("                       , REPLACE(REPLACE(FIRSTNAME, CHR(10), ''), CHR(13), '')) FIRSTNAME 	\n")
                             .append("     , DECODE(LASTNAME, 'WAP_TEMP', ''                                                    	\n")
                             .append("                      , 'SIM_TEMP', ''                                                    	\n")
                             .append("                      , REPLACE(REPLACE(LASTNAME, CHR(10), ''), CHR(13), '')) LASTNAME    	\n")
                             .append("     , DECODE(GENDER, 'W', ''                                                             	\n")
                             .append("                    , 'S', ''                                                             	\n")
                             .append("                    , DECODE(GENDER, 'M', '1', '2')) GENDER                               	\n")
                             .append("     , DECODE(BIRTH, 'WAP_TEMP', ''                                                       	\n")
                             .append("                   , 'SIM_TEMP', ''                                                       	\n")
                             .append("                   , REPLACE(REPLACE(BIRTH, CHR(10), ''), CHR(13), '')) BIRTH             	\n")
                             .append("     , EMAIL                                                                              	\n")
                             .append("     , HP_BRAND                                                                           	\n")
                             .append("     , HP_MODEL                                                                           	\n")
                             .append("     , HP_NUM                                                                             	\n")
                             .append("     , DECODE(HP_PROVIDER, '0000000', '', HP_PROVIDER) HP_PROVIDER                        	\n")
                             .append("	   , CASE																					\n")
                             .append("			WHEN length(TRIM(HP_BUY_YM)) < 6  THEN ''											\n")
                             .append("			WHEN length(TRIM(HP_BUY_YM)) > 6  THEN substr(HP_BUY_YM,0,6)						\n")
                             .append("			WHEN translate(trim(hp_buy_ym), '0123456789', '||||||||||') != '||||||'  THEN ''	\n")
                             .append("			WHEN length(TRIM(HP_BUY_YM)) = 6  THEN TRIM(HP_BUY_YM)								\n")
                             .append("			ELSE TRIM(HP_BUY_YM) END AS HP_BUY_YM												\n")
                             .append("     , DECODE(B.NEWSLETTER, 1, 'Y', 'N') NEWSLETTER                                       	\n")
                             .append("     , DECODE(B.SMS, 1, 'Y', 'N') SMS                                                     	\n")
                             .append("     , TO_CHAR(CREATED, 'YYYYMMDDHH24MISS')                                               	\n")
                             .append("     , DECODE(SUBSTR(MEMBERGBN,1,5),'05314','WAP','Web')                                  	\n")
                             .append("  FROM G_MB_MEMBER           A                                                            	\n")
                             .append("     ,(SELECT    USER_NO                                                                  	\n")
                             .append("               , SUM(DECODE(CODE, '0113001', 1, 0)) NEWSLETTER                            	\n")
                             .append("               , SUM(DECODE(CODE, '0113002', 1, 0)) SMS                                   	\n")
                             .append("         FROM    G_MB_OPTIN                                                               	\n")
                             .append("        GROUP BY USER_NO)    B                                                            	\n")
                             .append(" WHERE A.USER_NO     =  B.USER_NO(+)                                                      	\n")
                             .append("   AND A.REG_COUNTRY =  'gbr'                                                             	\n")
                             .append("   AND A.DELETED     IS NULL                         	                                    	\n")
                             .append("   AND A.EMAIL     IS NOT NULL                                                            	\n") 
                             .append("   AND A.EMAIL     <> 'WAP_TEMP'                                                          	\n")
                             .append("   AND A.EMAIL     <> 'null'                                                          		\n")                             
                             .append("   AND A.CREATED     <   TRUNC(TO_DATE('" + TODAY + "', 'YYYYMMDD'))                      	\n");
                             //.append("   AND A.CREATED     <  TRUNC(SYSDATE)                                                    \n");

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@eu-anydb:1521:ORAEU", "gsms", "any02gs");
            conn.setAutoCommit(true);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sQuery.toString());
			rsCnt = rs.getMetaData().getColumnCount();

            oswFile = new OutputStreamWriter(new FileOutputStream(fileNm, false), "ISO-8859-1");

            while(rs.next()){
				sbTemp.setLength(0);

                for(int i=1;i<=rsCnt;i++) {
                    sbTemp.append(ObjectUtils.toString(rs.getObject(i), ""));

                    if( i == rsCnt ){
                        sbTemp.append("\n");
                    }
                    else{
                        sbTemp.append("|");
                    }
                }

                oswFile.write(sbTemp.toString());
                cnt++;

                if(cnt % 500 == 0){
                    Logger.getLogger("udb.makeUserFileForGbrCRM").info("here we are == " + cnt);
                }
            }

            Logger.getLogger("udb.makeUserFileForGbrCRM").info("Total Count : " + cnt);
        }
        catch(Exception ex) {
            ex.printStackTrace();

            Logger.getLogger("udb.makeUserFileForGbrCRM").info(ex.toString());
            StackTraceElement[] stElement = ex.getStackTrace();
            for(int p=0; p<stElement.length;p++){
                Logger.getLogger("udb.makeUserFileForGbrCRM").info(stElement[p].toString());
            }
        }
        finally {
            if (rs   != null) try { rs.close(); }   catch (Exception e) { e.printStackTrace(); }
            if (stmt != null) try { stmt.close(); } catch (Exception e) { e.printStackTrace(); }
            if (conn != null) try { conn.close(); } catch (Exception e) { e.printStackTrace(); }
            if (oswFile != null) try { oswFile.close(); } catch (Exception e) { e.printStackTrace(); }
        }
    }
}
